package com.xm.dao.reception_desk;

import com.xm.entity.CommodityInsurance;
import com.xm.entity.CommodityInsuranceType;
import com.xm.entity.CommodityInsuranceArticle;
import com.xm.exception.SqlErrorException;
import com.xm.util.BaseUtil;
import org.intellij.lang.annotations.Language;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 商品保险表Dao
 * @author 康文程
 * @date 2024-1-5 上午 08:15
 */
public class CommodityInsuranceDao extends BaseUtil {
    /**
     * 根据ID查询商品保险信息
     * @param insuranceId 保险ID
     * @return CommodityInsurance 商品保险信息
     */
    public CommodityInsurance getInsuranceById(Integer insuranceId) {
        @Language("sql")
        String sql = "SELECT i.insurance_id, i.insurance_title, i.insurance_context, i.add_price, " +
                "t.insurance_type_logo " +
                "FROM commodity_insurance i " +
                "JOIN commodity_insurance_type t ON i.insurance_type_id = t.insurance_type_id " +
                "WHERE i.insurance_id = ?";
        CommodityInsurance insurance = null;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, insuranceId);
            while (rs.next()) {
                insurance = new CommodityInsurance();
                insurance.setInsuranceId(rs.getInt("i.insurance_id"));
                insurance.setInsuranceTitle(rs.getString("i.insurance_title"));
                insurance.setInsuranceContext(rs.getString("i.insurance_context"));
                insurance.setAddPrice(rs.getBigDecimal("i.add_price"));

                CommodityInsuranceType insuranceType = new CommodityInsuranceType();
                insuranceType.setInsuranceTypeLogo(rs.getString("t.insurance_type_logo"));

                insurance.setInsuranceType(insuranceType);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }

        return insurance;
    }

    /**
     * 根据多个insuranceIds查询除insuranceIds以外的保险
     * @param insuranceIds 多个保险Id
     * @param commodityId 商品保险
     * @return List<对象>
     */
    public List<CommodityInsurance> getInsurancesByIds(String insuranceIds,Integer commodityId) {
        @Language("sql")
        String sql = "SELECT i.insurance_id, i.insurance_preferential, i.insurance_title, i.insurance_context, i.add_price " +
                "FROM commodity_insurance i " +
                "WHERE i.insurance_id NOT IN (?) and commodity_id = ?";

        List<CommodityInsurance> insurances = new ArrayList<>();
        ResultSet rs = null;
        String separate = ",";
        try {
            if (insuranceIds.contains(separate)) {
                // insuranceIds 包含多个值
                String[] ids = insuranceIds.split(separate);
                StringBuilder condition = new StringBuilder();
                for (int i = 0; i < ids.length; i++) {
                    if (i > 0) {
                        condition.append(", ");
                    }
                    condition.append("?");
                }
                sql = sql.replaceFirst("\\?", condition.toString());
                Object[] params = new Object[ids.length + 1];
                System.arraycopy(ids, 0, params, 0, ids.length);
                params[ids.length] = commodityId;
                rs = executeQuery(sql, params);
            } else {
                // insuranceIds 只有一个值
                rs = executeQuery(sql, insuranceIds, commodityId);
            }

            while (rs.next()) {
                CommodityInsurance insurance = new CommodityInsurance();
                insurance.setInsuranceId(rs.getInt("i.insurance_id"));
                insurance.setInsuranceTitle(rs.getString("i.insurance_title"));
                insurance.setInsuranceContext(rs.getString("i.insurance_context"));
                insurance.setAddPrice(rs.getBigDecimal("i.add_price"));
                insurance.setInsurancePreferential(rs.getBigDecimal("i.insurance_preferential"));

                insurances.add(insurance);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }

        return insurances;
    }
    /**
     * 根据ID查询商品保险信息及关联表信息
     * @param insuranceId 保险ID
     * @return CommodityInsurance 商品保险信息
     */
    public CommodityInsurance getInsuranceWithRelatedTablesById(Integer insuranceId) {
        @Language("sql")
        String sql = "SELECT ci.insurance_id, ci.insurance_title, ci.insurance_context, ci.add_price, " +
                "cit.insurance_type_logo, ia.article_title, ia.article_context " +
                "FROM commodity_insurance ci " +
                "JOIN commodity_insurance_type cit ON ci.insurance_type_id = cit.insurance_type_id " +
                "LEFT JOIN insurance_article ia ON cit.insurance_type_id = ia.insurance_type_id " +
                "WHERE ci.insurance_id = ?";
        CommodityInsurance insurance = null;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, insuranceId);
            while (rs.next()) {
                insurance = new CommodityInsurance();
                insurance.setInsuranceId(rs.getInt("ci.insurance_id"));
                insurance.setInsuranceTitle(rs.getString("ci.insurance_title"));
                insurance.setInsuranceContext(rs.getString("ci.insurance_context"));
                insurance.setAddPrice(rs.getBigDecimal("ci.add_price"));

                CommodityInsuranceType insuranceType = new CommodityInsuranceType();
                insuranceType.setInsuranceTypeLogo(rs.getString("cit.insurance_type_logo"));

                CommodityInsuranceArticle insuranceArticle = new CommodityInsuranceArticle();
                insuranceArticle.setArticleTitle(rs.getString("ia.article_title"));
                insuranceArticle.setArticleContext(rs.getString("ia.article_context"));

                insurance.setInsuranceType(insuranceType);
                insurance.setInsuranceArticle(insuranceArticle);
            }
        } catch (SqlErrorException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(rs);
        }

        return insurance;
    }

}
